﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Data;

namespace WindowsFormsApplication1.Controller
{
    class Controller
    {
        public void addCustomer(string c_ID, string c_name, string c_lastName, string c_email, string c_phone, string c_bill)
        {
            SqlCeConnection conn = null;

            try
            {
                string det = "('" + c_ID + "','" + c_name + "','" + c_lastName + "','" + c_email + "','" + c_phone + "','" + c_bill + "')";
                conn = new SqlCeConnection("Data Source = Model\\Database1.sdf;");
                conn.Open();
                SqlCeCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO Customer ([customerID], [cFirstName], [cLastName], [cEmail], [cPhoneNo], [cbillDetails]) Values"+det;
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable getCustomerTable()
        {
            DataTable dt = new DataTable();
            SqlCeConnection conn = null;

            try
            {
                conn = new SqlCeConnection("Data Source = Model\\Database1.sdf;");
                conn.Open();
                string query = "SELECT * FROM Customer";
                SqlCeDataAdapter dA = new SqlCeDataAdapter(query, conn);
                SqlCeCommandBuilder cB = new SqlCeCommandBuilder(dA);
                dA.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getSupplierTable()
        {
            DataTable dt = new DataTable();
            SqlCeConnection conn = null;

            try
            {
                conn = new SqlCeConnection("Data Source = Model\\Database1.sdf;");
                conn.Open();
                string query = "SELECT * FROM Supplier";
                SqlCeDataAdapter dA = new SqlCeDataAdapter(query, conn);
                SqlCeCommandBuilder cB = new SqlCeCommandBuilder(dA);
                dA.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getOrderItemTable()
        {
            DataTable dt = new DataTable();
            SqlCeConnection conn = null;

            try
            {
                conn = new SqlCeConnection("Data Source = Model\\Database1.sdf;");
                conn.Open();
                string query = "SELECT * FROM orderItem";
                SqlCeDataAdapter dA = new SqlCeDataAdapter(query, conn);
                SqlCeCommandBuilder cB = new SqlCeCommandBuilder(dA);
                dA.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
    }
}
